package com.dbecom.platform.foundation.batch.writer;

import java.math.BigInteger;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.batch.core.ExitStatus;
import org.springframework.batch.core.StepExecution;
import org.springframework.batch.core.StepExecutionListener;
import org.springframework.batch.item.ItemWriter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.StringUtils;

import com.dbecom.platform.foundation.batch.model.CatalogGroupDTO;

public class CatalogGroupWriter implements ItemWriter<CatalogGroupDTO>, StepExecutionListener {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private StepExecution stepExecution;

    private Map<String, BigInteger> catalogMap = new HashMap<String, BigInteger>();

    @Override
    public void write(List<? extends CatalogGroupDTO> catalogGroupDTOs) throws Exception {
        for (CatalogGroupDTO catalogGroupDTO : catalogGroupDTOs) {
            System.out.println(catalogGroupDTO.toString());

            // initialize catalog
            BigInteger catalogKey = catalogMap.get(catalogGroupDTO.getCatalogId());
            if (StringUtils.isEmpty(catalogKey)) {
                try {
                    catalogKey = jdbcTemplate.queryForObject(
                            "select CATALOG_KEY from CATALOG where CATALOG_ID = ? and MARKFORDELETE = 0",
                            BigInteger.class, catalogGroupDTO.getCatalogId());
                } catch (EmptyResultDataAccessException ex) {
                    this.stepExecution.setFilterCount(this.stepExecution.getFilterCount() + 1);
                    // throw new ValidationException("Catalog is not existed : "
                    // + ex);
                    continue;
                }

                catalogMap.put(catalogGroupDTO.getCatalogId(), catalogKey);
            }

            // check parent group, if it is not existed, throw exception and
            // skip
            BigInteger parentGroupKey = null;
            if (!StringUtils.isEmpty(catalogGroupDTO.getParentGroupId())) {
                try {
                    parentGroupKey = jdbcTemplate.queryForObject(
                            "select CATGROUP_KEY from CATGROUP where CATGROUP_ID = ? and MARKFORDELETE = 0",
                            BigInteger.class, catalogGroupDTO.getParentGroupId());
                } catch (EmptyResultDataAccessException ex) {
                    this.stepExecution.setFilterCount(this.stepExecution.getFilterCount() + 1);
                    // throw new
                    // ValidationException("Parent group is not existed, it should be imported firstly : "
                    // + catalogGroupDTO.toString());
                    continue;
                }
            }

            // check delete flag: 1=YES, 0=NO
            if (catalogGroupDTO.getDelete() == "1") {
                // delete process
                jdbcTemplate.update("update CATGROUP set MARKFORDELETE = 1");
                continue;
            }

            // check group existed or not, if existed do update, else insert
            BigInteger catalogGroupKey = null;
            try {
                catalogGroupKey = jdbcTemplate.queryForObject(
                        "select CATGROUP_KEY from CATGROUP where CATGROUP_ID = ? and MARKFORDELETE = 0",
                        BigInteger.class, catalogGroupDTO.getGroupId());
            } catch (EmptyResultDataAccessException ex) {
                catalogGroupKey = null;
            }

            if (StringUtils.isEmpty(catalogGroupKey)) {
                // insert group
                jdbcTemplate.update(
                        "insert into CATGROUP (MEMBER_ID, CATGROUP_ID, MARKFORDELETE, LASTUPDATE, PUBLISHED, DISPLAY, FIELD1, FIELD2) "
                                + "values (?,?,?,?,?,?,?,?)", catalogGroupDTO.getMemberId(),
                        catalogGroupDTO.getGroupId(), catalogGroupDTO.getDelete(), new Date(),
                        catalogGroupDTO.getPublished(), catalogGroupDTO.getDispaly(), catalogGroupDTO.getField1(),
                        catalogGroupDTO.getField2());

                catalogGroupKey = jdbcTemplate.queryForObject(
                        "select CATGROUP_KEY from CATGROUP where CATGROUP_ID = ? and MARKFORDELETE = 0",
                        BigInteger.class, catalogGroupDTO.getGroupId());

                // insert group description
                jdbcTemplate
                        .update("insert into CATGRPDESC (LANGUAGE_ID, CATGROUP_KEY, NAME, SHORTDESCRIPTION, LONGDESCRIPTION, THUMBNAIL, FULLIMAGE, KEYWORD, NOTE) "
                                + "values (?,?,?,?,?,?,?,?)", catalogGroupDTO.getLanguageId(), catalogGroupKey,
                                catalogGroupDTO.getName(), catalogGroupDTO.getShortDescription(),
                                catalogGroupDTO.getLongDescription(), catalogGroupDTO.getThumbnail(),
                                catalogGroupDTO.getFullImage(), catalogGroupDTO.getKeyword(), catalogGroupDTO.getNote());
            } else {
                jdbcTemplate
                        .update("update CATGROUP set MEMBER_ID = ?,  MARKFORDELETE = ?, LASTUPDATE = ?, PUBLISHED = ?,  DISPLAY = ?, FIELD1 = ?, FIELD2 = ? where CATGROUP_ID = ?",
                                catalogGroupDTO.getMemberId(), catalogGroupDTO.getDelete(), new Date(),
                                catalogGroupDTO.getPublished(), catalogGroupDTO.getDispaly(),
                                catalogGroupDTO.getField1(), catalogGroupDTO.getField2(), catalogGroupKey);

                // check description by catalogGroupKey and languageId
                int count = jdbcTemplate.queryForObject(
                        "select count(1) from CATGRPDESC where LANGUAGE_ID = ? and CATGROUP_KEY = ?", Integer.class,
                        catalogGroupDTO.getLanguageId(), catalogGroupKey);

                if (count > 0) {
                    // update existed description
                    jdbcTemplate
                            .update("update CATGRPDESC set NAME = ?,  SHORTDESCRIPTION = ?, LONGDESCRIPTION = ?, THUMBNAIL = ?, FULLIMAGE = ?,  KEYWORD = ?, NOTE = ? where LANGUAGE_ID = ? and CATGROUP_KEY = ? ",
                                    catalogGroupDTO.getName(), catalogGroupDTO.getShortDescription(),
                                    catalogGroupDTO.getLongDescription(), catalogGroupDTO.getThumbnail(),
                                    catalogGroupDTO.getFullImage(), catalogGroupDTO.getKeyword(),
                                    catalogGroupDTO.getNote(), catalogGroupDTO.getLanguageId(), catalogGroupKey);
                } else {
                    // insert new description
                    jdbcTemplate
                            .update("insert into CATGRPDESC (LANGUAGE_ID, CATGROUP_KEY, NAME, SHORTDESCRIPTION, LONGDESCRIPTION, THUMBNAIL, FULLIMAGE, KEYWORD, NOTE) "
                                    + "values (?,?,?,?,?,?,?,?)", catalogGroupDTO.getLanguageId(), catalogGroupKey,
                                    catalogGroupDTO.getName(), catalogGroupDTO.getShortDescription(),
                                    catalogGroupDTO.getLongDescription(), catalogGroupDTO.getThumbnail(),
                                    catalogGroupDTO.getFullImage(), catalogGroupDTO.getKeyword(),
                                    catalogGroupDTO.getNote());
                }
            }

            // check is topGroup or not
            if (!StringUtils.isEmpty(catalogGroupDTO.getTopGroup())
                    && "TRUE".equals(catalogGroupDTO.getTopGroup().toUpperCase().trim())) {
                // update topGroup, if count is 0 then insert it to topGroup
                int updatedCount = jdbcTemplate.update(
                        "update CATTOGRP set LASTUPDATE = ?,  SEQUENCE = ? where CATALOG_KEY = ? and CATGROUP_KEY= ?",
                        new Date(), catalogGroupDTO.getSequence(), catalogKey, catalogGroupKey);

                if (updatedCount == 0) {
                    jdbcTemplate.update(
                            "insert into CATTOGRP (CATALOG_KEY, CATGROUP_KEY, LASTUPDATE, SEQUENCE) values (?,?,?,?)",
                            catalogKey, catalogGroupKey, new Date(), catalogGroupDTO.getSequence());
                }
            } else {
                // delete topGroup
                jdbcTemplate.update("delete from CATTOGRP where CATALOG_KEY = ? and CATGROUP_KEY= ?", catalogKey,
                        catalogGroupKey);
            }

            // check parent group
            if (!StringUtils.isEmpty(catalogGroupDTO.getParentGroupId())) {
                // update group relation, if count is 0 then insert one relation
                int updatedCount = jdbcTemplate
                        .update("update CATGRPREL set LASTUPDATE = ?,  SEQUENCE = ? where CATGROUP_KEY_PARENT = ? and CATGROUP_KEY_CHILD = ? and CATALOG_KEY = ?",
                                new Date(), catalogGroupDTO.getSequence(), parentGroupKey, catalogGroupKey, catalogKey);

                if (updatedCount == 0) {
                    jdbcTemplate
                            .update("insert into CATGRPREL (CATGROUP_KEY_PARENT, CATGROUP_KEY_CHILD, CATALOG_KEY, LASTUPDATE, SEQUENCE) values (?,?,?,?,?)",
                                    parentGroupKey, catalogGroupKey, catalogKey, new Date(),
                                    catalogGroupDTO.getSequence());

                } else {
                    // delete group relation
                    jdbcTemplate
                            .update("delete from CATGRPREL where CATGROUP_KEY_PARENT = ? and CATGROUP_KEY_CHILD = ? and CATALOG_KEY = ?",
                                    parentGroupKey, catalogGroupKey, catalogKey);
                }
            }

        }// end loop

    }// end write process

    @Override
    public void beforeStep(StepExecution stepExecution) {
        this.stepExecution = stepExecution;
    }

    @Override
    public ExitStatus afterStep(StepExecution stepExecution) {
        return null;
    }

}